﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using UlsStudio.Engine.SqlParsers;

namespace UlsStudio.Engine.Database
{
    /// <summary>
    /// Provides database functionality
    /// </summary>
    public class DbHelper
    {
        #region Privates

        private static SQLiteConnection _con = null;
        private static bool _dbCreated = false;
        private static string _dbFileName = string.Empty;

        #endregion

        #region Data Operations

        internal static void AddLine(AbstractUlsLogEntry line, SQLiteCommand insertCommand)
        {
            insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_TIMESTAMP + ""].Value = line.DateTime;
            insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_PROCESS + ""].Value = line.Process;
            insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_MSG + ""].Value = line.Message;
            insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_PRODUCT + ""].Value = line.Area;
            insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_CATEGORY + ""].Value = line.Category;
            if (line.Correlation == Guid.Empty)
                insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_CORRELATION + ""].Value = null;
            else
                insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_CORRELATION + ""].Value = line.Correlation.ToString();

            insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_EVENTID + ""].Value = line.EventID;
            insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_LEVEL + ""].Value = line.Level;
            insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_SOURCE + ""].Value = line.Source;
            insertCommand.Parameters[@"@" + Engine.Constants.FIELD_NAME_TID + ""].Value = line.ThreadID;

            insertCommand.ExecuteNonQuery();

        }

        /// <summary>
        /// Executes SQL and returns a reader
        /// </summary>
        public static SQLiteDataReader LoadItemsFromSQL(string sql, SQLiteConnection con)
        {
            SQLiteCommand cmd = DbHelper.GetInsertCommand(con);
            cmd.CommandText = sql;

            Logging.Writeline("DbHelper.LoadItemsFromSQL(): Executing SQL '{0}'.", cmd.CommandText);
            SQLiteDataReader r = cmd.ExecuteReader();

            return r;
        }

        /// <summary>
        /// Get record-count for a query.
        /// </summary>
        internal static int GetItemCount(SQLiteConnection con, SimpleSqlSelectStatementInfo q)
        {
            bool haveId = q.FieldCollection.Contains("id");

            SQLiteCommand cmd = DbHelper.GetInsertCommand(con);
            if (haveId)
            {
                cmd.CommandText = string.Format("select count(id) from ({0})", q.ToString());
            }
            else
            {
                cmd.CommandText = string.Format("select count(*) from ({0})", q.ToString());
            }
            

            Logging.Writeline("DbHelper.GetItemCount(): Executing SQL '{0}'.", cmd.CommandText);

            SQLiteDataReader r = cmd.ExecuteReader();
            if (r.Read())
            {
                int count = r.GetInt32(0);
                return count;
            }
            else
            {
                throw new InvalidOperationException("No record-count received");
            }
        }

        #endregion

        #region SQLite Objects
        internal static SQLiteCommand GetInsertCommand(SQLiteConnection connection)
        {
            string insertSQL = "INSERT INTO " + 
                "  [" + Engine.Constants.TABLE_NAME_LOGDATA + "] (" +
                "  [" + Engine.Constants.FIELD_NAME_TIMESTAMP + "]" +
                ", [" + Engine.Constants.FIELD_NAME_PROCESS + "]" +
                ", [" + Engine.Constants.FIELD_NAME_MSG + "]" +
                ", [" + Engine.Constants.FIELD_NAME_PRODUCT + "]" +
                ", [" + Engine.Constants.FIELD_NAME_CATEGORY + "]" +
                ", [" + Engine.Constants.FIELD_NAME_CORRELATION + "]" +
                ", [" + Engine.Constants.FIELD_NAME_EVENTID + "]" +
                ", [" + Engine.Constants.FIELD_NAME_LEVEL + "]" +
                ", [" + Engine.Constants.FIELD_NAME_SOURCE + "]" +
                ", [" + Engine.Constants.FIELD_NAME_TID + "]" +
            ") VALUES(" +
                "  @" + Engine.Constants.FIELD_NAME_TIMESTAMP + "" +
                ", @" + Engine.Constants.FIELD_NAME_PROCESS + "" +
                ", @" + Engine.Constants.FIELD_NAME_MSG + "" +
                ", @" + Engine.Constants.FIELD_NAME_PRODUCT + "" +
                ", @" + Engine.Constants.FIELD_NAME_CATEGORY + "" +
                ", @" + Engine.Constants.FIELD_NAME_CORRELATION + "" +
                ", @" + Engine.Constants.FIELD_NAME_EVENTID + "" +
                ", @" + Engine.Constants.FIELD_NAME_LEVEL + "" +
                ", @" + Engine.Constants.FIELD_NAME_SOURCE + "" +
                ", @" + Engine.Constants.FIELD_NAME_TID + "" +
            ")";
            SQLiteCommand cmd = new SQLiteCommand(insertSQL, connection);

            // Add params
            SQLiteParameter timeStampParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_TIMESTAMP, System.Data.DbType.DateTime);
            cmd.Parameters.Add(timeStampParam);

            SQLiteParameter processParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_PROCESS, System.Data.DbType.String);
            cmd.Parameters.Add(processParam);

            SQLiteParameter msgParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_MSG, System.Data.DbType.String);
            cmd.Parameters.Add(msgParam);

            SQLiteParameter productParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_PRODUCT, System.Data.DbType.String);
            cmd.Parameters.Add(productParam);

            SQLiteParameter categoryParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_CATEGORY, System.Data.DbType.String);
            cmd.Parameters.Add(categoryParam);

            SQLiteParameter correlationParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_CORRELATION, System.Data.DbType.String);
            cmd.Parameters.Add(correlationParam);

            SQLiteParameter eventIdParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_EVENTID, System.Data.DbType.String);
            cmd.Parameters.Add(eventIdParam);

            SQLiteParameter levelParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_LEVEL, System.Data.DbType.String);
            cmd.Parameters.Add(levelParam);

            SQLiteParameter sourceParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_SOURCE, System.Data.DbType.String);
            cmd.Parameters.Add(sourceParam);

            SQLiteParameter tidParam = new SQLiteParameter(@"@" + Engine.Constants.FIELD_NAME_TID, System.Data.DbType.String);
            cmd.Parameters.Add(tidParam);

            return cmd;
        }

        public static SQLiteConnection GetAndOpenConnection()
        {
            if (_con == null)
            {
                string dbConnectionString = @"Data Source=" + DbHelper.DbFileName + ";Cache Size=16777216;";
                _con = new SQLiteConnection(dbConnectionString);
                _con.Open();

                SQLiteCommand cmd = new SQLiteCommand("PRAGMA journal_mode = MEMORY", _con);
                cmd.ExecuteNonQuery();

                cmd.CommandText = "PRAGMA temp_store=MEMORY";
                cmd.ExecuteNonQuery();

                // http://sqlite.org/pragma.html#pragma_synchronous
                cmd.CommandText = "PRAGMA synchronous=OFF";
                cmd.ExecuteNonQuery();

                // Do everything in a transaction for speed. We don't care about persistance
                SQLiteCommand transCmd = new SQLiteCommand("BEGIN", _con);
                transCmd.ExecuteNonQuery();
            }

            return _con;
        }
        #endregion

        /// <summary>
        /// Whether a database has been setup
        /// </summary>
        internal static bool DbCreated
        {
            get { return DbHelper._dbCreated; }
        }

        /// <summary>
        /// Filename used for the database
        /// </summary>
        public static string DbFileName
        {
            get { return DbHelper._dbFileName; }
        }

        #region Schema Operations

        /// <summary>
        /// Creates a new temporary database file for SQLite to work with.
        /// </summary>
        internal static SQLiteConnection CreateTempDatabase()
        {
            if (DbHelper.DbCreated)
            {
                throw new InvalidOperationException("Database already created");
            }

            _dbFileName = System.IO.Path.GetTempFileName() +  "slb2.s3db";

            Logging.Writeline("Start-up: Creating DB file '{0}'", _dbFileName);
            SQLiteConnection.CreateFile(_dbFileName);

            // Open connection to database
            SQLiteConnection sqliteCon = DbHelper.GetAndOpenConnection();

            CreateTable(sqliteCon);

            _dbCreated = true;
            return sqliteCon;
        }

        private static void CreateTable(SQLiteConnection con)
        {

            // Define the SQL Create table statement
            string createAppUserTableSQL = "CREATE TABLE" + 
                " [" + Engine.Constants.TABLE_NAME_LOGDATA + "] (" +
                " [" + Engine.Constants.FIELD_NAME_ID + "]          INTEGER     PRIMARY KEY " +
                ",[" + Engine.Constants.FIELD_NAME_TIMESTAMP + "]   DateTime        NULL" +
                ",[" + Engine.Constants.FIELD_NAME_PROCESS + "]     varchar(50)     NULL" +
                ",[" + Engine.Constants.FIELD_NAME_MSG + "]         Text            NULL" +
                ",[" + Engine.Constants.FIELD_NAME_PRODUCT + "]     varchar(50)     NULL" +
                ",[" + Engine.Constants.FIELD_NAME_CATEGORY + "]    varchar(50)     NULL" +
                ",[" + Engine.Constants.FIELD_NAME_CORRELATION + "] varchar(50)     NULL" +
                ",[" + Engine.Constants.FIELD_NAME_EVENTID + "]     varchar(50)     NULL" +
                ",[" + Engine.Constants.FIELD_NAME_LEVEL + "]       varchar(50)     NULL" +
                ",[" + Engine.Constants.FIELD_NAME_SOURCE + "]      varchar(50)     NULL" +
                ",[" + Engine.Constants.FIELD_NAME_TID + "]         varchar(50)     NULL" +
                ")";

            Logging.Writeline("DbHelper.CreateTable(): Executing SQL '{0}'.", createAppUserTableSQL);

            // Create the table
            SQLiteCommand createCommand = new SQLiteCommand(createAppUserTableSQL, con);
            createCommand.ExecuteNonQuery();
            createCommand.Dispose();

        }

        internal static void ResetData(SQLiteConnection con)
        {
            // Delete old table
            SQLiteCommand cmd = DbHelper.GetInsertCommand(con);
            cmd.CommandText = "delete from [" + Engine.Constants.TABLE_NAME_LOGDATA + "]";
            Logging.Writeline("DbHelper.ResetData(): Executing SQL '{0}'.", cmd.CommandText);
            SQLiteDataReader r = cmd.ExecuteReader();

        }

        #endregion

        /// <summary>
        /// Destroys the app DB context and cleans up.
        /// </summary>
        public static void Dispose()
        {
            string dbFile = UlsStudio.Engine.Database.DbHelper.DbFileName;
            Console.Write("Cleanup: Attempting to delete temp db-file '{0}'...", dbFile);

            DbHelper._con.Close();
            DbHelper._con.Dispose();

            SQLiteConnection.ClearAllPools();

            
            try
            {
                System.IO.File.Delete(dbFile);
                Logging.Writeline("success.");
            }
            catch (Exception ex)
            {
                Logging.Writeline("failed - {0}.", ex.Message);
            }
        }
    }
}
